Configure PostgreSQL databases

This guide describes how to configure PostgreSQL databases.

In this topic:

PostgreSQL folders

The following information will help you use the PostgreSQL database with StarTeam Server:

• It is installed in the pgsql folder under the StarTeam Server folder.
• The service name is StarTeam-PostgreSQL.
• The superuser login name is postgres.
• The following StarTeam Server functionality is not available for PostgreSQL: Online Purge, Maintenance Scheduler, and the Import/Export Manager.

When connecting to a PostgreSQL database server on Microsoft Windows, the Microsoft Windows path should include the path to the PostgreSQL bin directory. If the path to the PostgreSQL libraries is not set correctly, a message similar to the following one will appear:

Libpq.dll: The specified module could not be found. 
pq.dll: The specified module could not be found.
DBMS API Library loading fails. This library is a part of DBMS client installation, not SQLAPI++.
Make sure DBMS client is installed and this required library is available for dynamic loading.

When connecting to a PostgreSQL database server on Microsoft Windows, the Microsoft Windows system path should include the path to the PostgreSQL bin directory. For example, for a default installation, add this to the System PATH variable: C:\Program Files\PostgreSQL\9.3\bin.

Backups

We recommend using the pg_dump utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). The most flexible output file formats is the directory format (-Fd). This format is compressed by default.

Back to top

Upgrade to PostgresQL 13 from PostgreSQL 9

Starteam Server 17.3 will optionally install PostgreSQL 13 on port 5433, and is backwards compatible with PostgreSQL 9.x.

To upgrade a configuration from PostgreSQL 9.x to 13:

1. Create a backup in the following directory: Maintenance>Database backups>PostgreSQL Backups
2. Install the StarTeam Server 17.3, make sure to select PostgreSQL installation under Custom.

3. Restore the backup of StarTeam PostgreSQL 9.x database to PostgreSQL 13.x, as described in the following steps:

   1. Install PGadmin 4.
   2. Connect to Postgresql 13 using port 5433.
   3. Create a database user with the same name and password as the one used for the StarTeam PostgreSQL 9.x database. Make sure you give the user the "can login" and "Inherit right from the parent roles" privileges.
   4. Create the database with the same name as the one used in the StarTeam PostgreSQL 9.x database.
   5. Set the owner of the database to the user you created in step c.

   6. Use the pg_restore command to restore the database backup to the new PostgreSQL 13 database and set the port to 5433:

      Copy code

      pg_restore -U postgres -h < Hostname> -p 5433 -d <Db Name> StarTeam.backup 

4. Upgrade the StarTeam Repository and database.

   1. Point the Server configuration to the new PostgreSQL 13 instance by changing the port:

      Open the starteam-server-configs.xml file in the StarTeam Server installation directory, and then change or edit the dbport to 5433:

      <option name="DBPort" value="5433"/>

   2. Shutdown the PostgreSQL 9 Windows service with the name pgsql.
   3. Open the Server Administration and select your configuration, make sure database port in the Server/Configuration Properties/Database Connection Information is set to 5433 and then click Verify Connection.

   4. Run database upgrade if required, and then start the server.

      Tip: You can use the StarTeam Migration.

5. If Pulse Code review was configured before the upgrade, do the following:

   1. Backup the Pulse database from the existing PostgreSQL 9 database and then restore it into the PostgreSQL 13 database.
   2. Create a corresponding new Pulse user on the PostgreSQL 13 database.
   3. Modify the database.jdbcurl property to connect to the database port 5433 in the <StarTeam Server install>/Pulse/startup.properties directory.

Back to top

Install PostgreSQL 13 on Linux RedHat 8

Go to the following site to download and install PostgreSQL 13: https://www.postgresql.org/download/linux/redhat/

It is recommend that you install the pgadmin 4. You can download the pgadmin 4 at: https://www.tecmint.com/install-postgresql-and-pgadmin-in-rhel-8/

For instructions on how to install PostgreSQL 13, see https://www.postgresql.org/. When the installation asks for the location of PostgreSQL directory, use the following: /usr/pgsql-13.

Back to top

Create a server configuration (for an existing database)

The first time you start a new server configuration, StarTeam Server creates all tables in the database you specify. This section explains how to create a StarTeam Server configuration using a previously created PostgreSQL database. Database names should:

• Begin with a letter.
• Contain letters and numbers only.
• Not contain spaces.
• Not be a SQL reserved word such as create, delete, if, then, else, or goto.

1. Start the Server Administration tool. Click Start > Programs > Micro Focus > StarTeam Server <version> > StarTeam Server. The Server Administration tool opens.
2. Click Server > New Configuration. The New Configuration dialog box opens.

3. Enter the new configuration data:

   1. Type the name of the configuration in the Configuration Name field. If you want the server configuration to have the same name as the database (a nice convention, especially if you have several server configurations), you must follow the database naming conventions explained at the beginning of this section.
   2. Type or click Browse to specify the Repository Path location to be used to store log files and other information. If the repository path that you enter does not exist, the application creates it for you. The Repository Path is also the location for the default hive.
   3. Select PostgreSQL from the Database Type list.
   4. Uncheck the option to Create new StarTeam database, so that StarTeam Server will not automatically create the database for it.

   5. Create an initial hive for the Native-II vault by doing one of the following:

      Accept the default settings

      Leave the Default option selected and proceed to the next step. With the default settings, StarTeam Server: Creates an initial hive named DefaultHive. Creates subfolders on the repository path named Archives and Cache to be used by the DefaultHive. Stipulates that the maximum cache size is 20% of the space currently available on the drive on which the cache is located. Uses the default setting of 600 seconds (10 minutes) between cache cleanups. Uses the default setting of 95% for the storage threshold, the point at which this drive is considered full.

      Specify custom values

      Select the Custom option and change any of the hive settings.

   6. Click Next, and enter the Database Server name, Database name, Database login name, and password in the appropriate text boxes.
   7. Optionally, if you are using a port other than the default, check Edit Database Port and type the port number in the text field.
   8. Click Verify Connection to test the connection. If the connection fails, review and change your settings.
   9. Click Finish. This action re-displays the Server Administration tool, which shows your new server configuration as a child of the Local node.

      Note: In addition to creating the server configuration, StarTeam Server adds information about the new server configuration to your starteam-server-configs.xml file. For more information about this file, see the Server Administration Tool Help.

   10. Optionally, if you are using a port other than the default, check Edit Database Port and type the port number in the text field.
   11. Click Verify Connection to test the connection. If the connection fails, review and change your settings.
   12. Click Finish. This action re-displays the Server Administration tool, which shows your new server configuration as a child of the Local node.

       Note: In addition to creating the server configuration, StarTeam Server adds information about the new server configuration to your starteam-server-configs.xml file. For more information about this file, see the Server Administration Tool Help.

4. By default, all server configurations are set to use the TCP/IP endpoint (port) 49201. However, each server configuration on a given computer must have a unique endpoint so it is recommended that you edit the default endpoint. To change the endpoint:

   1. Select the server configuration.
   2. Click the Start with Override button (or click Actions > Start with Override from the main menu). The Start with Override dialog box opens.
   3. Enter the endpoint that you want to use in the TCP/IP Endpoint field, and click OK.
5. Be sure to configure your new server configuration (for information, see the Server Administration Tool Help) and plan a backup schedule for it.

Back to top

Create and start a server configuration (new database)

The first time you start a new server configuration, StarTeam Server creates all tables in the database you specify. This section explains how to create a server configuration and start it for the first time. It assumes that you want the server to automatically create a PostgreSQL database. If that is not the case, see Create a database manually. Database names should:

• Begin with a letter.
• Contain letters and numbers only.
• Not contain spaces.
• Not be a SQL reserved word such as create, delete, if, then, else, or goto.

1. Start the Server Administration tool. Click Start > Programs > Micro Focus > StarTeam Server <version> > StarTeam Server. The Server Administration tool opens.
2. Click Server > New Configuration. The New Configuration dialog box opens.
3. Enter the new configuration data:
   
   1. Type the name of the configuration in the Configuration Name field. If you want the server configuration to have the same name as the database (a nice convention, especially if you have several server configurations), you must follow the database naming conventions explained at the beginning of this section.
   2. Type or click Browse to specify the Repository Path location to be used to store log files and other information. If the repository path that you enter does not exist, the application creates it for you. The Repository Path is also the location for the default hive.
   3. Select PostgreSQL from the Database Type list.
   4. Check the option to Create new StarTeam database, so that StarTeam Server automatically creates the database.
   5. Create an initial hive for the Native-II vault by doing one of the following:

      Accept the default settings

      Leave the Default option selected and proceed to the next step. With the default settings, StarTeam Server: Creates an initial hive named DefaultHive. Creates subfolders on the repository path named Archives and Cache to be used by the DefaultHive. Stipulates that the maximum cache size is 20% of the space currently available on the drive on which the cache is located. Uses the default setting of 600 seconds (10 minutes) between cache cleanups. Uses the default setting of 95% for the storage threshold, the point at which this drive is considered full.

      Specify custom values

      Select the Custom option and change any of the hive settings.

   6. Click Next to create the PostgreSQL database.

4. Enter the server and database information:

   1. Enter the name in the Database Server name field.
   2. Type or click Browse to specify the names of the computer and the database on your network that should be used.
   3. Enter the password for the system administrator in the Database Server name field. The initial default system administrator password is postgres.
   4. Click Verify Connection to test the connection. If the connection fails, review and change your settings.
   5. Click Next.
5. Enter the information for creating the data files and transaction logs, and click Finish.

   Note: We recommend keeping the data files and transaction log files on different physical drives under their own disk controllers.

   The default settings are appropriate for your use if you have fewer than 15 users and expect to store 1GB or less data.

6. By default, all server configurations are set to use the TCP/IP endpoint (port) 49201. However, each server configuration on a given computer must have a unique endpoint so it is recommended that you edit the default endpoint. To change the endpoint:

   1. Select the server configuration.
   2. Click the Start with Override button (or click Actions > Start with Override from the main menu). The Start with Override dialog box opens.
   3. Enter the endpoint that you want to use in the TCP/IP Endpoint field, and click OK.
7. Be sure to configure your new server configuration (for information, see the Server Administration Tool Help) and plan a backup schedule for it.

Back to top

SQL scripts for PostgreSQL databases

StarTeam Server comes with some SQL scripts written specifically for use with your database. These scripts help you maintain and tune StarTeam Server databases. You run some SQL scripts after installation, some on a weekly basis for database performance maintenance, and some scripts are run for you automatically by StarTeam Server.

The SQL scripts for PostgreSQL databases that you may run are located in the Micro Focus\StarTeam Server <version>\DBScripts\postgresql_Scripts folder.

starteam_postgresql_dropall.sql

Run: only if necessary.

One use case example is if you migrate a StarTeam Server configuration to another database, you might use this script to remove tables from the original database. Another example is if you mistakenly add the StarTeam Server tables to a tablespace other than the StarTeam Server tablespace, use this script to remove them.

Back to top

Create a database manually

Despite the fact that StarTeam Server has automated PostgreSQL database creation, you may prefer to create your own. This makes more sense for PostgreSQL because there are good tools for database creation. Database names should:

• Begin with a letter.
• Contain letters and numbers only.
• Not contain spaces.
• Not be a SQL reserved word such as create, delete, if, then, else, or goto.

To create your own PostgreSQL server database:

1. Install PostgreSQL Server.
2. Install StarTeam Server. See StarTeam Server on Windows installation .

3. Create a StarTeam Server database. Contact your database administrator about the specifics. Be sure that:

   • The database is owned by a StarTeam Server user.
   • The name of the database follows the conventions explained earlier in this section.
   • The template for the database is template0.
   • The encoding for the database should be: UTF-8. This refers to the physical storage of character strings in the database.
4. Create and start a server configuration. For details, see Create a server configuration (for an existing database).

Back to top